package com.course_design.util;

import java.io.File;


import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.course_design.entity.Student;

public class TeacherExcelUtil{
	
	
	public List<Student> readXls(File file,Integer classInfo) throws Exception {
		InputStream inputStream=new FileInputStream(file);
		HSSFWorkbook hssfWorkbook=new HSSFWorkbook(inputStream);
		List<Student> students=new ArrayList<>();
		for(int numSheet=0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++) {
			HSSFSheet sheet=hssfWorkbook.getSheetAt(numSheet);
			if(sheet.getPhysicalNumberOfRows()==0) {
				break;
			}
			
			Student student =new Student();
			for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++) {
				HSSFRow row=sheet.getRow(rowNum);
				
				if(row!=null) {
					
					HSSFCell num=row.getCell(0);
					student.setStudentnum((String)getValueHSSFCell(num));
					
					HSSFCell name=row.getCell(1);
					student.setStudentname((String)getValueHSSFCell(name));
					
					HSSFCell sex=row.getCell(2);
					student.setSex((Byte) getValueHSSFCell(sex));
					
					HSSFCell age=row.getCell(3);
					student.setAge((Integer) getValueHSSFCell(age));
					
					HSSFCell studentPhone=row.getCell(4);
					student.setStudentphone((String) getValueHSSFCell(studentPhone));
					
					HSSFCell qq=row.getCell(5);
					student.setStudentqq((String) getValueHSSFCell(qq));
					student.setScore(100);
					student.setClassinfo(classInfo);
					}
					
				}
				//把构造好的对象加入list
				students.add(student);
			}
		return students;
	}
	
	
	public List<Student> readXlsx(File file,Integer classInfo) throws Exception {
		InputStream inputStream=new FileInputStream(file);
		XSSFWorkbook hssfWorkbook=new XSSFWorkbook(inputStream);
		List<Student> students=new ArrayList<>();
		for(int numSheet=0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++) {
			XSSFSheet sheet=hssfWorkbook.getSheetAt(numSheet);
			if(sheet.getPhysicalNumberOfRows()==0) {
				break;
			}
			for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++) {
				XSSFRow row=sheet.getRow(rowNum);
				Student student =new Student();
				if(row!=null) {
					XSSFCell num=row.getCell(0);
					student.setStudentnum((String)getValueXSSFCell(num));
					
					XSSFCell name=row.getCell(1);
					student.setStudentname((String)getValueXSSFCell(name));
					
					XSSFCell sex=row.getCell(2);
					byte sexInteger=0;
					if(getValueXSSFCell(sex).equals("男")) {
						sexInteger=1;
					}
					student.setSex(sexInteger);
					
					XSSFCell agexss=row.getCell(3);
					String ageString=(String)getValueXSSFCell(agexss);
					Integer age=Integer.parseInt(ageString.substring(0, 2));
					student.setAge(age);
					
					XSSFCell studentPhone=row.getCell(4);
					student.setStudentphone((String) getValueXSSFCell(studentPhone));
					
					XSSFCell qq=row.getCell(5);
					student.setStudentqq((String) getValueXSSFCell(qq));
					student.setScore(100);
					student.setClassinfo(classInfo);
					}
				//把构造好的对象加入list
				students.add(student);
				}
				
			}
		return students;
	}
	public Object getValueHSSFCell(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
        	HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
            return dataFormatter.formatCellValue(hssfCell);
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
 public Object getValueXSSFCell(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
 
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
        	HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
            return dataFormatter.formatCellValue(xssfCell);
        } else {
 
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }
	
}
